<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;
class MySQLDatabase extends SQLDatabase {
    private $dbh;

    protected static $sql_var = array(
        'current_date_utc'       => "utc_date()",
        'current_date_local'     => "curdate()",
        'current_datetime_utc'   => "utc_timestamp()",
        'current_datetime_local' => "now()"
    );

    protected function _connect () {
        global $trace;
        if ($trace) trace_entry();
        #echo "_connect() : $this->dbuser/$this->dbpass@$this->dbhost\n";

        $matches = array();
        if (isset($this->dsn) && $this->dsn !== '') {
            if (preg_match('!^([^/]+)/([^/]+)$!', $this->dsn, $matches)) {
                $dbhost      = $matches[1];
                $schema_name = $matches[2];
            }
        }
        if ((!isset($dbhost)      || $dbhost      === '') && isset($this->dbhost))      $dbhost      = $this->dbhost;
        if ((!isset($schema_name) || $schema_name === '') && isset($this->schema_name)) $schema_name = $this->schema_name;

        if (!isset($dbhost)       || $dbhost      === '') throw new \Exception("Could not determine the appropriate host for the requested database ($this->name).");
        if (!isset($schema_name)  || $schema_name === '') throw new \Exception("Could not determine the appropriate schema for the requested database ($this->name).");
   
        $this->dbh = mysql_connect($dbhost, $this->dbuser, $this->dbpass);
        #echo "_connect() : connected [$this->dbh]\n";
        if (!$this->dbh) throw new \Exception("Could not connect to database host ($dbhost) using user ($this->dbuser): " + mysql_error());
        $success = mysql_select_db($schema_name);
        #echo "_connect() : selected $schema_name [$success]\n";
        if (!$success) throw new \Exception("Could not select the requested schema ($schema_name).");
        if ($trace) trace_exit();
    }

    protected function _disconnect () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dbh)) {
            mysql_close($this->dbh);
        }
        if ($trace) trace_exit();
    }

    protected function _get_schema_defs_from_db () {
        global $trace;
        if ($trace) trace_entry();
        $sql = "select schema_name, 'Y' as physical_ind from information_schema.schemata order by schema_name";
        $schema_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'schema_name');
        if ($trace) trace_exit($schema_defs);
        return($schema_defs);
    }

    protected function _get_table_defs_from_db ($schema) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $sql = "select t.table_name from information_schema.tables t where t.table_schema = '$schema' and (t.table_type like '%TABLE%' or t.table_type like '%VIEW%') order by t.table_name";
        $rows = $this->get_rows($sql);
        $table_defs = Array();
        foreach ($rows as $row) {
            $table_name = strtolower($row[0]);
            $table_defs[$table_name] = Array('table_name' => $table_name);
        }
        if ($trace) trace_exit($table_defs);
        return($table_defs);
    }

    protected function _get_column_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $sql = "select c.column_name, c.column_type, c.ordinal_position as order_idx, c.column_default as default_value, (case when c.is_nullable = 'NO' then 'Y' else 'N' end) as not_null_ind, coalesce(c.character_maximum_length,c.numeric_precision) as max_length, (case c.column_key when 'PRI' then 'Y' else 'N' end) as primary_key_ind, 'Y' as physical_ind from information_schema.columns c where c.table_schema = '$schema' and c.table_name = '$table' order by c.ordinal_position";
        $column_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'column_name');
        foreach ($column_defs as $column_name => $column_def) {
            $column_defs[$column_name]['column_type'] = $this->native_type_to_app_type($column_def['column_type']);
        }
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

    protected function _get_index_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        $sql = "select (case i.constraint_name when 'PRIMARY' then concat(i.table_name,'.PRIMARY') else i.constraint_name end) as index_name from information_schema.table_constraints i where i.table_schema = '$schema' and i.table_name = '$table' and constraint_type in ('PRIMARY KEY','UNIQUE') order by constraint_type, constraint_name";
        $index_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'index_name');
        if ($trace) trace_exit($index_defs);
        return($index_defs);
    }

    protected function _get_index_columns_from_db ($schema, $table, $index_defs, $column_defs) {
        global $trace;
        if ($trace) trace_entry();
        $all_index_rows = Array();
        #foreach ($index_defs as $index_name => $index_def) {
        #    $index_rows = $this->get_rows("pragma table_info('$index_name')");
        #    foreach ($index_rows as $index_row) {
        #        $row = Array();
        #        $row['order_idx']       = $index_row[0]+1;
        #        $row['index_id']        = $index_def['index_id'];
        #        $row['column_id']       = $column_defs[$index_row[2]]['column_id'];
        #        $all_index_rows[]       = $row;
        #    }
        #}
        if ($trace) trace_exit($all_index_rows);
        return($all_index_rows);
    }

    protected function _get_relationships_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        #$rows = $this->get_rows("pragma foreign_key('$table')");    # cid|name|type|notnull|dflt_value|pk
        $relationships = Array();
        #foreach ($rows as $row) {
        #    $relationship = Array();
        #    $relationship['order_idx']       = $row[0]+1;
        #    $relationship['column_name']     = strtolower($row[1]);
        #    $relationship['column_type']     = $this->native_type_to_app_type(strtolower($row[2]));
        #    $relationship['max_length']      = $row[3] ? $row[3] : null;
        #    $relationship['default_value']   = $row[4] ? $row[4] : null;
        #    $relationship['primary_key_ind'] = $row[5] ? 'Y' : 'N';
        #    $relationship['physical_ind'] = 'Y';
        #    $relationships[$relationship['column_name']] = $relationship;
        #}
        if ($trace) trace_exit($relationships);
        return($relationships);
    }

#    protected function _get_native_column_defs ($table, $schema_name) {
#        global $trace;
#        if ($trace) trace_entry();
#        $sql = "select column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length,\n       numeric_precision, numeric_scale, column_type, column_key, extra, privileges as max_length\nfrom information_schema.columns\nwhere table_schema = '$schema_name'\n  and table_name = '$table'\norder by ordinal_position";
#        $column_defs = $this->get_hashes($sql);
#        if ($trace) trace_exit($column_defs);
#        return($column_defs);
#    }
#
#    protected function _get_column_defs ($table, $schema_name, &$table_def) {
#        global $trace;
#        if ($trace) trace_entry();
#        $fulltable = "$schema_name.$table";
#
#        if (! isset($table_def['columns'])) $table_def['columns'] = array();
#        $columns =& $table_def['columns'];
#           
#        if (!isset($table_def['column'])) {
#            $table_def['column'] = Array();
#        }
#        $column_defs =& $table_def['column'];
#
#        $native_column_defs = $this->_get_native_column_defs($table, $schema_name);
#        # $table_def['native_column_defs'] = $native_column_defs;    # (I'm not sure we really need to keep this around)
#
#        $table_def['primary_key'] = Array();
#        $primary_key =& $table_def['primary_key'];
#
#        $column_alias_used = Array();
#        for ($i = 0; $i < count($native_column_defs); $i++) {
#            $native_column_def = $native_column_defs[$i];
#            $native_column = $native_column_def['column_name'];
#            $column = strtolower($native_column);
#
#            if (! isset($column_defs[$column])) $column_defs[$column] = array();
#            $column_def =& $column_defs[$column];
#           
#            if (! isset($column_def['alias'])) {
#                $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#            }
#            else {
#                $alias = $column_def['alias'];
#                if (isset($column_alias_used[$alias])) {
#                    $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#                }
#            }
#            if (! isset($column_def['label'])) $column_def['label'] = $this->make_label($column);
#
#            if (! isset($column_def['writeable']) &&
#                $native_column_def['extra'] != 'auto_increment')
#                $column_def['writeable'] = 1;
#
#            if (! isset($column_def['type'])) {
#                $native_data_type = $native_column_def['data_type'];
#                if ($native_data_type == 'date' ||
#                    $native_data_type == 'datetime' ||
#                    $native_data_type == 'time') {
#                    $column_def['type'] = $native_data_type;
#                }
#                elseif ($native_data_type == 'timestamp') {
#                    $column_def['type'] = 'datetime';
#                }
#                elseif (substr($native_data_type,0,3) == 'int' ||
#                        ($native_data_type == 'number' && $native_column_def['numeric_scale'] == 0)) {
#                    $column_def['type'] = 'integer';
#                }
#                elseif ($native_data_type == 'number' ||
#                        $native_data_type == 'float' ||
#                        $native_data_type == 'double') {
#                    $column_def['type'] = 'float';
#                }
#                elseif (! isset($native_column_def['character_maximum_length']) ||
#                        $native_column_def['character_maximum_length'] < 512) {
#                    $column_def['type'] = 'string';
#                }
#                else {
#                    $column_def['type'] = 'text';
#                }
#            }
#
#            if (! isset($column_def['maxlength'])) {
#                if ($native_column_def['character_maximum_length']) {
#                    $column_def['maxlength'] = $native_column_def['character_maximum_length'];
#                }
#                elseif ($native_column_def['numeric_precision']) {
#                    $column_def['maxlength'] = $native_column_def['numeric_precision'] + 2;  # approx. accomodates "-" (negative) prefix and decimal point
#                }
#            }
#
#            if (! in_array($column, $columns))
#                array_push($columns, $column);
#
#            if ($native_column_def['column_key'] == 'PRI') {
#                array_push($primary_key, $column);
#                $column_def['primary_key'] = 1;
#            }
#        }
#        if ($trace) trace_exit();
#    }
#
##    protected function _get_indexes ($table, $schema_name, &$table_def) {
#        global $trace;
#        if ($trace) trace_entry();
#        # NOTE: $table_def['primary_key'] is already built
#        $table_def['unique_indexes'] = Array();
#        $table_def['indexes']        = Array();
#        if ($trace) trace_exit();
#    }

    protected function _get_select_sth ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sql  = $this->_get_select_sql($table, $params, $columns, $options);
        if ($debug_sql) fwrite($debug_fh, "<!-- DEBUG_SQL:\n" . $sql . " -->\n");
        $sth = mysql_query($sql, $this->dbh);
        if (!$sth) {
            $message = 'Error in SQL: [' . mysql_errno() . '] ' . mysql_error();
            if (preg_match('/; check the manual that corresponds to your MySQL server version for the right syntax to use/',$message)) {
                $message = preg_replace('/; check the manual that corresponds to your MySQL server version for the right syntax to use/','',$message);
            }
            throw new \Exception($message . ' : ' . $sql);
        }
        if (isset($options) && isset($options['query_def'])) {
            if (! is_array($options['query_def'])) $options['query_def'] =  array();
            $query_def            =& $options['query_def'];
            $query_def['column']  =  array();
            $column_defs          =& $query_def['column'];
            $query_def['columns'] =  array();
            $columns              =& $query_def['columns'];
            $num_fields = mysql_num_fields($sth);
            $i = 0;
            while ($i < $num_fields) {
                $meta = mysql_fetch_field($sth, $i);
                if (!$meta) {
                    throw new \Exception("ERROR: fetching metadata for column {$i} : " . $sql);
                }
                $column_name = strtolower($meta->name);
                $column_defs[$column_name] = array('column_name'  => $column_name,
                                                   'column_label' => $this->make_label($column_name),
                                                   'column_type'  => $this->native_type_to_app_type($meta->type));
                $columns[] = $column_name;
                $i++;
            }
        }
        if ($trace) trace_exit();
        return($sth);
    }

    public function get_rows ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $rows = array();
        $row_idx = 0;
        $row = mysql_fetch_array($sth, MYSQL_NUM);
        while ($row) {
            if ($debug_sql >= 2) fwrite($debug_fh, '<!-- DEBUG_SQL: [' . implode(',', $row) . "]\n");
            $rows[$row_idx] = $row;
            $row = mysql_fetch_array($sth, MYSQL_NUM);
            $row_idx++;
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($rows) . "] -->\n");
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_hashes ($table, $params = null, $columns = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $rows = array();
        $row_idx = 0;
        $row = mysql_fetch_array($sth, MYSQL_ASSOC);     // CAUTION: case-sensitive. I may need to fall back and do this myself.
        while ($row) {
            if ($debug_sql >= 2) {
                fwrite($debug_fh, '<!-- DEBUG_SQL: [');
                $first = 1;
                foreach ($row as $key => $value) {
                    if (!$first) fwrite($debug_fh, ',');
                    fwrite($debug_fh, "$key=>$value");
                    $first = 0;
                }
                fwrite($debug_fh, "]\n");
            }
            $rows[$row_idx] = $row;
            $row = mysql_fetch_array($sth, MYSQL_ASSOC); // CAUTION: case-sensitive. I may need to fall back and do this myself.
            $row_idx++;
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($rows) . "] -->\n");
        if ($trace) trace_exit(count($rows).' rows');
        return($rows);
    }

    public function get_column ($table, $params = null, $column = null, &$options = null) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        $sth  = $this->_get_select_sth($table, $params, $columns, $options);
        $column_data = array();
        $row_idx = 0;
        $row = mysql_fetch_array($sth, MYSQL_NUM);
        while ($row) {
            if ($debug_sql >= 2) fwrite($debug_fh, '<!-- DEBUG_SQL: [' . implode(',', $row) . "]\n");
            $column_data[$row_idx] = $row[0];
            $row = mysql_fetch_array($sth, MYSQL_NUM);
            $row_idx++;
        }
        if ($debug_sql) fwrite($debug_fh, '<!-- DEBUG_SQL: rows=[' . count($column_data) . "] -->\n");
        if ($trace) trace_exit($column_data);
        return($column_data);
    }

    public function execute ($sql) {
        global $trace, $debug_sql, $debug_fh;
        if ($trace) trace_entry();
        if (preg_match('/\\{.*\\}/',$sql)) $sql = $this->substitute_sql_vars($sql);
        if ($debug_sql) fwrite($debug_fh, "<!-- DEBUG_SQL:\n" . $sql . "\n");
        #echo "SQLDatabase.execute($sql)\n";
        mysql_query($sql, $this->dbh);
        $error_code = mysql_errno($this->dbh);
        if (!$error_code) {
            $nrows = mysql_affected_rows($this->dbh);
            $error_code = mysql_errno($this->dbh);
        }
        else {
            $nrows = 0;
        }
        #echo "SQLDatabase.execute()=$nrows\n";
        if ($error_code) {
            $error_msg         = mysql_error($this->dbh);
            throw new \Exception("ERROR: Database($this->name).execute(): [$error_code] : $error_msg : $sql");
        }
        if ($debug_sql) fwrite($debug_fh, "<!-- DEBUG_SQL: rows=[$nrows] -->\n");
        if ($trace) trace_exit($nrows);
        return($nrows);
    }

    protected function _make_limit_clause ($table, $options) {
        global $trace;
        if ($trace) trace_entry();
        $limit_clause = '';
        if (isset($options['limit']) && $options['limit']) {
            $limit_clause = "\nlimit " . $options['limit'];
            if (isset($options['offset']) && $options['offset'] > 0) {
                $limit_clause .= " offset " . $options['offset'];
            }
        }
        if ($trace) trace_exit($limit_clause);
        return($limit_clause);
    }

    public function _quote ($str_value) {
        $str_value = mysql_real_escape_string($str_value);
        return("'" . $str_value . "'");
    }

    public function date_literal ($year, $month, $day) {
        return("'$year-$month-$day'");
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        return("'$year-$month-$day $hours:$min:$sec'");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("date_add(curdate(),INTERVAL $days DAY)");
        else                    return("date_add('$base_date',INTERVAL $days DAY)");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        if ($days == intval($days)) {
            if ($base_datetime == null) return("date_add(now(),INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
            else                        return("date_add('$base_datetime',INTERVAL '$days $hours:$min:$sec' DAY_SECOND)");
        }
        else {
            if ($base_datetime == null) return("date_add(now(),INTERVAL $days DAY)");
            else                        return("date_add('$base_datetime',INTERVAL $days DAY)");
        }
    }
}

